﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Odbc;
using System.Data;
using System.Xml.Linq;
using System.Data.SqlClient;

namespace SONORA2012
{
	public class SNRDB
	{

		
		//public SNRTable oSNRTable = new SNRTable();

		public static string dbDSNName = "SNRDB";
		public static string dbUserName = "PROGRAMMER";
		public static string dbUserPassword = "apr";
		public static string SQLConnStr = null;
		public static string SQLCommand = null;
		public string MSG = null;

		[SerializableAttribute]
		public class SNRTable
		{

			string MSG = null;
			public List<SNRColumns> Columns = new List<SNRColumns>();
			public string TableName = null;
			public string TableDescription = null;

			//Constructor
			public SNRTable(string tableName)
			{
				if (GetTableInfo(tableName, out TableDescription, out Columns, out MSG))
				{
					TableName = tableName;
				}
				else
				{
					
				}
			}

			public bool Select(ref XDocument DataBox)
			{
				MSG = null;
				string SQLCommandString = null;
				DataTable queryDataTable = new DataTable();

				var iqueries = from queries in DataBox.Descendants("queries")
							   from query in queries.Descendants("query")
							   select query;
				foreach (XElement iquery in iqueries)
				{
					SQLCommandString = iquery.Attribute("name").Value;
				}

				var imessages = from main in DataBox.Descendants("databox")
							   from messages in main.Descendants("messages")
							   select messages;
				foreach (XElement imessage in imessages)
				{
					imessage.Remove();
					break;
				}

				XElement newTableElement = new XElement("messages");
				DataBox.Element("databox").Add(newTableElement);

				try
				{
					SQLConnStr = "Dsn=" + dbDSNName + ";uid=" + dbUserName + ";pwd=" + dbUserPassword;
					OdbcConnection newODBCConn = new OdbcConnection(SQLConnStr);
					OdbcCommand newODBCCom = new OdbcCommand();
					newODBCCom.Connection = newODBCConn;
					newODBCCom.CommandText = SQLCommandString;

					newODBCConn.Open();
					OdbcDataAdapter newDA = new OdbcDataAdapter(newODBCCom);
					DataSet myDataSet = new DataSet();
					newDA.Fill(myDataSet);
					newDA.Fill(queryDataTable);
					newODBCConn.Close();
					
					FormatQueryResults(ref DataBox, queryDataTable);			
					AddStructToDataBox(ref DataBox);
					AddMessageToDataBox(ref DataBox, "lang_selecting_is_successful", "INFO");
				}
				catch
				{
					AddMessageToDataBox(ref DataBox, "lang_selecting_is_not_successful", "ERROR");
					return false;
				}
				
				return true;
			}

			public bool FormatQueryResults(ref XDocument iDataBox, DataTable iqueryDataTable)
			{
				System.IO.StringWriter writer = new System.IO.StringWriter();
				iqueryDataTable.TableName = "row";
				iqueryDataTable.WriteXml(writer, XmlWriteMode.IgnoreSchema, false);
				XElement xmlDataTable = XElement.Parse(writer.ToString());
				iDataBox.Element("databox").Elements("queryresults").Single().Add(xmlDataTable);

				var ides = from db in iDataBox.Descendants("databox")
						   from qrs in db.Descendants("queryresults")
						   from de in qrs.Descendants("DocumentElement")
						   select de;
				foreach (XElement ide in ides)
				{
					ide.Name = TableName;
				}

				return true;
			}

			public bool AddMessageToDataBox(ref XDocument iDataBox, string message, string messageType)
			{
				XElement newMessageElement = new XElement("message",
															new XAttribute("name", message),
															new XAttribute("type", messageType)
															);
				iDataBox.Element("databox").Elements("messages").Single().Add(newMessageElement);

				return true;
			}

			public bool AddStructToDataBox(ref XDocument iDataBox)
			{
				System.IO.StringWriter sw = new System.IO.StringWriter();
				System.Xml.Serialization.XmlSerializer x = new System.Xml.Serialization.XmlSerializer(Columns.GetType());
				x.Serialize(sw, Columns);
				XElement xmlSerialize = XElement.Parse(sw.ToString());

				XElement newTableElement = new XElement("table",
														new XAttribute("name", TableName),
														new XAttribute("description", TableDescription)
													);
				iDataBox.Element("databox").Elements("construct").Single().Add(newTableElement);

				var a = from Users in xmlSerialize.Descendants("SNRColumns")
						select Users;
				foreach (XElement i in a)
				{
					iDataBox.Element("databox").Elements("construct").Single().Add(i);
				}

				return true;
			}

			public bool GetTableInfo(string TableName, out string TableDescription, out List<SNRColumns> ColumnList, out string MSG)
			{
				MSG = null;
				TableDescription = null;
				ColumnList = new List<SNRColumns>();
				int ColumnCount = 0;
				int FKeyCount = 0;
				int RuleCount = 0;

				// SQL bağlantısı oluşturmak için SQL bağlantı ayarlarını içeren bir string tanımlanıyor.
				SQLConnStr = "Dsn=" + dbDSNName + ";uid=" + dbUserName + ";pwd=" + dbUserPassword;
				OdbcConnection newODBCConn = new OdbcConnection(SQLConnStr);
				OdbcCommand newODBCCom = new OdbcCommand();
				newODBCCom.Connection = newODBCConn;

				// Tablo bilgilerinin çekildiği kod parçacığı...
				try
				{
					newODBCConn.Open();
					MSG = "lang_connection_is_successful";

					SQLCommand = "select * from SYS_TABLE where TABLE_NAME='" + TableName + "'";
					newODBCCom.CommandText = SQLCommand;

					OdbcDataAdapter newODBCAdapter = new OdbcDataAdapter(newODBCCom);
					DataTable dt = new DataTable();
					newODBCAdapter.Fill(dt);

					TableDescription = dt.Rows[0].ItemArray[1].ToString();		// Tablo açıklamasını verir...

					//foreach (DataRow row in dt.Rows)
					//{
					//    foreach (DataColumn col in dt.Columns)
					//        Console.WriteLine(row[col]);
					//    Console.WriteLine("".PadLeft(20, '='));
					//}

					newODBCConn.Close();
				}
				catch
				{
					MSG = "lang_connection_is_not_successful";
					return false;
				}

				// Yukarıdaki tabloya ait kolonların bilgilerinin çekildiği kod parçacığı...
				try
				{
					newODBCConn.Open();
					MSG = "lang_connection_is_successful";

					SQLCommand = "select * from SYS_COLUMN where TABLE_NAME='" + TableName + "' order by COLUMN_ORDER";
					newODBCCom.CommandText = SQLCommand;

					OdbcDataAdapter newODBCAdapterColumn = new OdbcDataAdapter(newODBCCom);
					DataTable dtColumn = new DataTable();
					newODBCAdapterColumn.Fill(dtColumn);

					ColumnCount = dtColumn.Rows.Count;

					for (int i = 0; i < ColumnCount; i++)
					{
						SNRColumns  column = new SNRColumns();

						column.TableName = dtColumn.Rows[i]["TABLE_NAME"].ToString();					// Tablo adını verir...
						column.Name = dtColumn.Rows[i]["COLUMN_NAME"].ToString();						// Kolon adını verir...
						column.DataType = dtColumn.Rows[i]["COLUMN_DATATYPE"].ToString();				// Kolon veri türünü verir...
						column.Default = dtColumn.Rows[i]["COLUMN_DEFAULT"].ToString();					// Kolon varsayılan değerini verir...
						column.Caption = dtColumn.Rows[i]["COLUMN_CAPTION"].ToString();					// Kolon başlığını verir...
						column.Length = Convert.ToInt32(dtColumn.Rows[i]["COLUMN_LEN"].ToString());		// Kolon veri uzunluğunu verir...
						column.Decimal = Convert.ToInt32(dtColumn.Rows[i]["COLUMN_DEC"].ToString());	// Kolon ondalık uzunluğunu verir...
						column.Mode = dtColumn.Rows[i]["COLUMN_MODE"].ToString();						// Kolon modunu (MAN, OPT, KEY) verir...
						column.Order = Convert.ToInt32(dtColumn.Rows[i]["COLUMN_ORDER"].ToString());	// Kolon sıralama numarasını verir...
						column.ControlType = dtColumn.Rows[i]["COLUMN_CONTROLTYPE"].ToString();			// Kolon kontrol türünü verir...

						/* Foreign Key İçin Select At   */
						SQLCommand = "select * from SYS_FOREIGNKEY where TABLE_NAME='" + TableName + "' and COLUMN_NAME='" + column.Name + "'";
						newODBCCom.CommandText = SQLCommand;

						OdbcDataAdapter newODBCAdapterFKey = new OdbcDataAdapter(newODBCCom);
						DataTable dtFKey = new DataTable();
						newODBCAdapterFKey.Fill(dtFKey);

						FKeyCount = dtFKey.Rows.Count;

						for (int j = 0; j < FKeyCount; j++)
						{
							column.RefTableName = dtFKey.Rows[j]["REFTABLE_NAME"].ToString();		// Referans olarak kabul ettiği tablonun adını verir...
							column.RefColumnName = dtFKey.Rows[j]["REFCOLUMN_NAME"].ToString();	// Referans olarak kabul ettiği tablodaki, referans kolonun adını verir...
							column.RefFormula = dtFKey.Rows[j]["REFFORMULA"].ToString();			// Referans olarak kabul ettiği tablodaki, referans değerini saklayan kolonun adını verir...
						}

						/* Validation Ruleları al   */
						SQLCommand = "select * from SYS_COLUMNRULE where TABLE_NAME='" + TableName + "' and COLUMN_NAME='" + column.Name + "' order by RULE";
						newODBCCom.CommandText = SQLCommand;

						OdbcDataAdapter newODBCAdapterRule = new OdbcDataAdapter(newODBCCom);
						DataTable dtRule = new DataTable();
						newODBCAdapterRule.Fill(dtRule);

						RuleCount = dtRule.Rows.Count;

						for (int k = 0; k < RuleCount; k++)
						{
							SNRValidationRule rule = new SNRValidationRule();
							rule.Rule = dtRule.Rows[k]["RULE"].ToString();			// Kuralı verir...
							rule.ErrMsg = dtRule.Rows[k]["ERRMSG"].ToString();		// Kurala uyulmadığında gösterilecek hatayı verir...
							column.Validator.Add(rule);
						}

						ColumnList.Add(column);

					}

					//foreach (DataRow row in dt.Rows)
					//{
					//    foreach (DataColumn col in dt.Columns)
					//        Console.WriteLine(row[col]);
					//    Console.WriteLine("".PadLeft(20, '='));
					//}

					newODBCConn.Close();
				}
				catch(Exception Ex)
				{
					MSG = Ex.Message + " / lang_connection_is_not_successful";
					return false;
				}

				return true;
			}

			public class clsPerson
			{
				public string FirstName;
				public string MI;
				public string LastName;
			}

			[SerializableAttribute]
			public class SNRColumns
			{
				public string TableName = null;
				public string Name = null;
				public string DataType = null;
				public string Default = null;
				public string Caption = null;
				public int Length = 0;
				public int Decimal = 0;
				public string Mode = null;
				public int Order = 0;
				public string ControlType = null;

				public string RefTableName = null;
				public string RefColumnName = null;
				public string RefFormula = null;

				public List<SNRValidationRule> Validator = new List<SNRValidationRule>();
			}

			[SerializableAttribute]
			public class SNRValidationRule
			{
				public string Rule = null;
				public string ErrMsg = null;
			}

		
		}


		
	}
}